﻿Module CheckDb
    Sub FixMovedOPCs()
        If MyPIDConn.PIDIniDBType = "ORACLE" Then
            'ORA
            SelectStr = <STRING>
    DECLARE 
            OPCID xxxSchemaName.T_OPC.SP_ID%Type;
            MateID xxxSchemaName.T_OPC.MatingOPCPath%Type;
            CURSOR OPCIDCursor IS SELECT OPC1.SP_ID, Rep2.SP_DrawingID FROM xxxSchemaName.T_OPC OPC1, xxxSchemaName.T_OPC OPC2, xxxSchemaName.T_Representation Rep1, xxxSchemaName.T_Representation Rep2 
            WHERE OPC1.SP_ID=Rep1.SP_ModelItemID AND OPC1.SP_pairedWithID = OPC2.SP_ID AND OPC2.SP_ID=Rep2.SP_ModelItemID AND Rep1.ItemStatus xxxnotequal 4 AND Rep2.ItemStatus xxxnotequal 4 AND OPC1.MatingOPCPath xxxnotequal Rep2.SP_DrawingID;
    BEGIN  
    OPEN OPCIDCursor; 
    LOOP
            FETCH OPCIDCursor INTO OPCID,MateID;  
            EXIT WHEN OPCIDCursor%NOTFOUND;
            Update xxxSchemaName.T_OPC set MatingOPCPath = MateID where SP_ID = OPCID;
            commit;
    End loop;
    CLOSE OPCIDCursor;          
    END;    
            </STRING>.Value
            Call MyPIDConn.PIDUpdateDataByDBA(MyPIDConn.PIDPlant_DBNAME, MyPIDConn.ActivePlantPIDApp_User, SelectStr)
        Else
            'SQL   
            'use InstanceName
            SelectStr = <STRING>

DECLARE @OPCID VARCHAR(50);
DECLARE @MateID VARCHAR(50);
DECLARE OPCIDCursor CURSOR for SELECT OPC1.SP_ID, Rep2.SP_DrawingID FROM xxxSchemaName.T_OPC OPC1, xxxSchemaName.T_OPC OPC2, xxxSchemaName.T_Representation Rep1, xxxSchemaName.T_Representation Rep2 WHERE OPC1.SP_ID=Rep1.SP_ModelItemID AND OPC1.SP_pairedWithID = OPC2.SP_ID AND OPC2.SP_ID=Rep2.SP_ModelItemID AND Rep1.ItemStatus xxxnotequal 4 AND Rep2.ItemStatus xxxnotequal 4 AND OPC1.MatingOPCPath xxxnotequal  Rep2.SP_DrawingID;
BEGIN
OPEN OPCIDCursor;
FETCH NEXT FROM OPCIDCursor INTO @OPCID,@MateID;
while @@FETCH_STATUS = 0
BEgin
FETCH NEXT FROM OPCIDCursor INTO @OPCID,@MateID;
Update xxxSchemaName.T_OPC set xxxSchemaName.T_OPC.MatingOPCPath = @MateID where SP_ID = @OPCID;
End;
CLOSE OPCIDCursor;
END;
   
            </STRING>.Value
            Call MyPIDConn.PIDUpdateDataByDBA(MyPIDConn.PIDPlant_DBNAME, MyPIDConn.ActivePlantPIDApp_User, SelectStr)
        End If
        MessageBox.Show("Done!")
    End Sub
    Sub DeleteWrongPipeLine()
        Dim sLineAttrOption() As String
        Dim dtCreateRefPID As New DataTable
        sLineAttrOption = FindInfoInOptionManager("Pipeline Name Attributes").Split(",")
        'step 2, read Column Name according to the Display Name
        SelectStr = <string>
                        SELECT t1.name,t1.displayname FROM xxxSchemaName.itemattributions t1
join xxxSchemaName.item t2 on t1.itemid=t2.id
WHERE t2.name='Pipeline'                       
                    </string>.Value

        MyPIDConn.PIDReadDataByDBA(MyPIDConn.PIDPlant_DBNAME, MyPIDConn.ActivePlantPIDAppDictionary_User, SelectStr, dtCreateRefPID)
        Dim sReadOptionsAttrs As String = ""
        For i = 0 To sLineAttrOption.Length - 1
            For Each dtFound As DataRow In dtCreateRefPID.Select("DISPLAYNAME='" & sLineAttrOption(i) & "'")
                sLineAttrOption(i) = dtFound("NAME")
                sReadOptionsAttrs = sReadOptionsAttrs + sLineAttrOption(i) + ","
            Next
        Next

        sReadOptionsAttrs = Mid(sReadOptionsAttrs, 1, sReadOptionsAttrs.Length - 1)

        'step 3, select all the pipeline form DB
        SelectStr = <STRING>
select T1.SP_ID,XXXReadOptions 
from xxxSchemaName.t_pipeline T1
JOIN xxxSchemaName.t_plantitem T2 ON T1.SP_ID=T2.SP_ID
ORDER BY XXXReadOptions
                    </STRING>.Value
        SelectStr = SelectStr.Replace("XXXReadOptions", sReadOptionsAttrs)
        dtCreate.Reset()
        MyPIDConn.PIDReadDataByDBA(MyPIDConn.PIDPlant_DBNAME, MyPIDConn.ActivePlantPIDApp_User, SelectStr, dtCreate)

        'step 4, find out the pipeline and delete it

        If dtCreate.Rows.Count = 0 Then
            MessageBox.Show("太牛逼了，没有任何一个需要删除的Line。")
            Exit Sub
        End If
        Dim sContent As String = "" : Dim sContentNext As String = ""
        For j = 1 To dtCreate.Columns.Count - 1  'no need for sp_id column
            sContent = sContent + dtCreate.Rows(0)(j).ToString + ","   'Base Row for comparation
        Next

        For i = 1 To dtCreate.Rows.Count - 1
            sContentNext = ""
            For j = 1 To dtCreate.Columns.Count - 1
                sContentNext = sContentNext + dtCreate.Rows(i)(j).ToString + ","   'Base Row for next pipeline
            Next

            If sContentNext = sContent Then
                'same name
                SelectStr = "delete from xxxSchemaName.t_pipeline where sp_id='" & dtCreate.Rows(i)("SP_ID").ToString & "'"
                Call MyPIDConn.PIDUpdateDataByDBA(MyPIDConn.PIDPlant_DBNAME, MyPIDConn.ActivePlantPIDApp_User, SelectStr)
            Else
                'no same name for this pipeline
                sContent = ""
                For j = 1 To dtCreate.Columns.Count - 1
                    sContent = sContent + dtCreate.Rows(i)(j).ToString + ","   'Base Row reset
                Next
            End If
        Next
        MessageBox.Show("Done!")
        dtCreateRefPID = Nothing
    End Sub
End Module
